<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2020 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\cms\controller;


use function PHPSTORM_META\type;
use think\admin\Controller;
use think\facade\Config;
use think\admin\service\AdminService;
use think\admin\extend\DataExtend;
use think\admin\service\MenuService;




/**
 * 培训学时管理
 * Class Eduhour
 * @package app\wechat\controller
 */
class Eduhour extends BaseController
{

    /**
     * 当前操作数据库
     * @var string
     */
    protected $table = 'CmsEduEnroll';
    static $PASS_STATE_OPTION = array(0=>"待考核",1=>"合格",2=>"不合格");

    /**
     * 学时管理
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function index()
    {

        $this->title = "学时管理";
        $map['hour'] = array("gt",0);

        $query = $this->_query($this->table)
            ->like('name,company,room_no')
            ->whereExp("hour",">0");

//        $query = $this->_query($this->table)->alias("ee")
//            ->join('cms_edu_cert ec', "ee.id=ec.class_id")
//            ->like('name,company,room_no')
//            ->whereExp("hour",">0");
  //         var_dump($this->app->db->name($this->table)->getLastSql());



        $class_name = $this->request->get("class_name");
        if($class_name){
            $class_ids = $this->_query_data_ids($class_name, "CmsEduClass");
            $query = $query->whereIn("class_id",$class_ids);
        }

        $this->time_span = $this->request->get("time_span");
        if($this->time_span) {
            $time_span = explode(" - ", $this->time_span);
            $query = $query->whereBetween("occur_time", [$time_span[0], $time_span[1]]);
        }
        $this->company = $this->request->get("company");
        $this->room_no = $this->request->get("room_no");


        $query->order('id desc')->page();
    }





    public function _query_data_ids($name, $tableName){
        $query = $this->app->db->name($tableName)->whereLike('name',"%".$name."%")
            ->field("id")->order('id desc')->select();
        $ids = array();
        for($i=0; $i<count($query); $i++){
            $ids[] = $query[$i]['id'];
        }
        return $ids;
    }

    protected function _index_page_filter(&$data)
    {
        if($data){
            $class_ids = $this->_getDataIds($data, "class_id");
            $class = $this->_getClassDict($class_ids);
            for($i=0; $i<count($data); $i++){
                $data[$i]['class_info'] = $class[$data[$i]['class_id']];
            }
        }
    }

    protected function _getClassDict($class_ids){
        $map = array("id"=> $class_ids);
        $class = $this->app->db->name('CmsEduClass')->field("id,name")->where($map)->select();
        $result = array();
        for ($i=0; $i<count($class); $i++){
            $result[$class[$i]['id']] = $class[$i];
        }
        return $result;
    }

    protected function _getDataIds($data, $key){
        $arr = [];
        for ($i=0; $i<count($data); $i++){
            array_push($arr, $data[$i][$key]);
        }
        return $arr;
    }

    /**
     * 表单数据处理
     * @param array $vo
     * @throws \ReflectionException
     */
    protected function _form_filter(&$vo)
    {
        if ($this->request->isGet()) {
            // 清理权限节点
            AdminService::instance()->clearCache();

            if($vo){
                $this->enroll = $vo;
            }else{
                $this->enroll = array(
                    "name"=>"",
                    "idno"=>"",
                    "gender"=>"",
                    "company"=>"",
                    "avatar"=>"",
                    "phone"=>"",
                    "class_id"=>"",
                    "position_id"=>"",
                );
            }
            //查出所有正在报名的培训班信息
            $this->classes = $this->_get_class_enroll();
            for($i=0; $i<count($this->classes); $i++){
                if($this->classes[$i]['id']==$this->enroll['class_id'])
                    $this->class = $this->classes[$i];
                $this->classes[$i]['sub'] = $this->_get_work_ids($this->classes[$i]['position_ids']);
            }
            $this->classesJson = json_encode($this->classes);

            if($vo && $this->class['position_ids']){
                $this->position = $this->_get_work_ids($this->class['position_ids']);
            }else{
                $this->position = [];
            }

        }
    }

    //查询所有正在接受报名的开班信息
    public function _get_class_enroll(){
        $map = array("status"=>1);
        return $this->app->db->name('CmsEduClass')->field("id,name,position_ids")->where($map)->select()->toArray();
    }

    public function _get_work_ids($position_ids){
        $work_ids = [];
        $position = $this->app->db->name('CmsEduWork')->alias("wp")
            ->field("wp.*,ew.name as class_name, ew.id as class_id")
            ->join('cms_edu_work ew', "ew.id=wp.parent_id")
            ->whereIn("wp.id", $position_ids)
            ->order('wp.id desc')->select()->toArray();

        for($i=0; $i<count($position); $i++){
            if(array_key_exists($position[$i]['class_id'], $work_ids)==false){
                $work_ids[ $position[$i]['class_id'] ] = array(
                    "id" => $position[$i]['class_id'],
                    "name" => $position[$i]['class_name'],
                    "sub" => [],
                );
            }
        }

        for($i=0; $i<count($position); $i++){
            $work_ids[ $position[$i]['class_id'] ]['sub'][] = $position[$i];
        }
        return array_values($work_ids);
    }

    /**
     * 添加学时信息
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function add()
    {

        if ($this->request->isGet()) {
            $this->_applyFormToken();
            $this->_form($this->table, 'form');
        } else {

            $data = $this->request->post('data', []);
            $map['no'] = $data['no'];
            $map['name'] = $data['name'];
            $enroll = $this->app->db->name($this->table)->where($map)->find();
            if($enroll==false){
                $this->error("报名号：".$data['no']."与姓名：".$data['name']."不匹配");
            }
            $to_insert = array(
                "hour" => $data['hour'],
            );
            if ($this->app->db->name($this->table)->where(['id'=>$enroll['id']])->update($to_insert)) {
                $this->success('添加成功！');
            } else {
                $this->error('添加失败，请稍候再试！');
            }
        }
    }

    /**
     * 编辑学时信息
     * @auth true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function edit()
    {

        $this->id = $this->request->get('id');
        $map = array("id" => $this->id);
        if (empty($this->id)) $this->error('参数错误，请稍候再试！');
        if ($this->request->isGet()) {
            if ($this->request->get('output') === 'json') {
                $enroll = $this->app->db->name($this->table)->where($map)->find();
                $this->success('获取数据成功！', $enroll);
            } else {
                $this->_applyFormToken();
                $this->fetch("form");
            }
        }else{
            $data = $this->request->post('data', []);
            $to_update = array("hour"=>$data['hour'],'cert'=>$data['cert']);
            if ($this->app->db->name($this->table)->where($map)->update($to_update) !== false) {
                $this->success('信息更新成功！');
            } else {
                $this->error('信息更新失败，请稍候再试！');
            }
        }

    }

    /**
     * 删除学时信息
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function remove()
    {
        $this->_delete($this->table);
    }

    /**
     * 导出学时信息
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function export(){

        $className = $this->request->get("class_name");
        $stuName = $this->request->get("student_name");
        $timeSpan = $this->request->get("time_span");
        $company = $this->request->get("company");
        $room_no = $this->request->get("room_no");
        $title = [
            array("no","报名号"), array("class_name","培训名称"),
            array("name","学员名称"),array("gender","性别"),array("city","市州"),
            array("idno","身份证号"),array("company","企业名称"),
            array("room_no","分配班级"),array("hour","已完成学时"),
        ];
        $data = $this->prepareExportData($stuName, $className, $timeSpan,$company,$room_no);
        $this->exportExcel($title, $data);

    }

    private function prepareExportData($stuName, $className, $timeSpan,$company,$room_no){

        $map = ['ee.is_deleted' => '0'];
        $query= $this->app->db->name($this->table)->whereExp("hour",">0");
        if($stuName) $query = $query->whereLike("ee.name","%".$stuName."%");
        if($className) $query = $query->whereLike("ec.name","%".$className."%");
        if($company) $query = $query->whereLike("ee.company","%".$company."%");
        if($room_no) $query = $query->whereLike("ee.room_no","%".$room_no."%");
        if($timeSpan){
            $timeSpan = explode(" - ", $timeSpan);
            $query = $query->whereBetween("occur_time",[$timeSpan[0],$timeSpan[1]]);
        }
        $res = $query ->alias("ee")
            ->field("ee.*,ec.name as class_name")
            ->join('cms_edu_class ec', "ec.id=ee.class_id")
            ->where($map)
            ->order('ee.id desc')
            ->select()->toArray();
        return $res;
    }

    /**
     * 导入学时信息
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function import(){
        //$inputFileName = "F:\\phpStudy\\GasPortalProject\\temp\\01simple.xlsx";
        //$inputFileName = "http://127.0.0.1:9500/upload/63/1fcfb478ee9a562f929784a1b44e64.xlsx";
        $inputFileName = $this->request->request("url");
        $inputFileName = $this->filerFilePath($inputFileName);

        $fields = [ "no", "name", "hour" ];
        $data = $this->getImportDataFromExcel($inputFileName, $fields);
        if($data==false){
            $this->error("没有数据需要被导入");
        }

        for ($i=0; $i<count($data); $i++) {
            $map['no'] = $data[$i]['no'];
            $map['name'] = $data[$i]['name'];
            if(!$this->app->db->name("CmsEduEnroll")->where($map)->find()){
                $this->error("报名号：".$data[$i]['no'],"与姓名：".$data[$i]['name']."不匹配");
            }
        }

        for ($i=0; $i<count($data); $i++) {
            $map = array("no"=>$data[$i]['no'],"name"=>$data[$i]['name']);
            $enroll = $this->app->db->name("CmsEduEnroll")->where($map)->find();

            if($this->app->db->name("CmsEduClassHours")->where(array("enroll_id"=>$enroll['id']))->find()){
                $to_update = array(
                    "hour" => $data[$i]['hour'],
                );
                $this->app->db->name($this->table)->where($map)->update($to_update);
            }else{
                $to_update = array(
                    "class_id" => $enroll['class_id'],
                    "enroll_id" => $enroll['id'],
                    "enroll_no" => $enroll['no'],
                    "hour" => $data[$i]['hour'],
                    "create_at" => time(),
                    "create_by" => $this->app->session->get('user.id'),
                );
                $this->app->db->name("CmsEduClassHours")->save($to_update);
            }

        }

        $this->success("导入成功");

    }

}
